package com.sh.ssao.lottery.config.poi;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;

/**
 *
 * EXCEL 工具
 * 上传Excel，第一行数据为表头，将数据处理为List<Map<String,Object>>对象
 */
@Component
@Slf4j
public class ExcelUtiles {

    /**
     * 处理Excel数据
     * @param file
     * @return
     */
    public List<Map<String,Object>> impDataAnalysis(File file){
        List<Map<String,Object>> list = new ArrayList<>();
        /*if (null !=file){
            //获取工作簿
            Workbook wb = getWorkbook(file);
            //获取sheet页
            int sheetunm = wb.getNumberOfSheets();

            for (int i = 0;i<sheetunm;i++){
                Sheet sheet =  wb.getSheetAt(i);
                //int b = sheet.getNumMergedRegions();
                int count = 0;
                int end = 0;//总列数
                //声明title
                List<String> tableTitle = new ArrayList<>();
                for (Row row : sheet) {
                    CourseDeclareBean courseDeclareBean = new CourseDeclareBean();

                    //获取总列数(空格的不计算)
                    int columnTotalNum = row.getPhysicalNumberOfCells();

                    //获取
                    if (count == 0) {
                        //获取总列数
                        end = row.getLastCellNum();
                        //处理数据表头
                        tableTitle = getList(end,row);
                        log.debug("***************************:"+tableTitle.toString()+"----"+end);
                    }else {
                        //处理数据
                        Map<String,Object> map = getMap(end,row,tableTitle);
                        if (null !=map){
                            list.add(map);
                        }else {
                            break;//空数据直接退出
                        }
                    }
                    count++;//记录行数
                }
            }
        }*/
        // 工作表
        Workbook workbook = getWorkbook(file);//WorkbookFactory.create(xlsFile);

        // 表个数。
        int numberOfSheets = workbook.getNumberOfSheets();

        // 遍历表。
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);

            // 行数。
            int rowNumbers = sheet.getLastRowNum() + 1;

            // Excel第一行。
            Row temp = sheet.getRow(0);
            if (temp == null) {
                continue;
            }

            int cells = temp.getPhysicalNumberOfCells();

            // 读数据。
            for (int row = 0; row < rowNumbers; row++) {
                Row r = sheet.getRow(row);
                for (int col = 0; col < cells; col++) {
                    System.out.print(r.getCell(col).toString());
                    Map<String, Object> map = new HashMap<>();
                    map.put("姓名",String.valueOf(r.getCell(col)).trim());
                    list.add(map);
                }

                // 换行。
                System.out.println();
            }
        }
        return list;
    }

    public static void main(String[] args) {
        /*List<Map<String, Object>> maps = new ExcelUtiles().impDataAnalysis(new File("d://原始数据_202112211402.xlsx"));
        System.out.println("----------start-------------");
        System.out.println(maps.toString());
        for (Map<String, Object> map : maps) {
            for (String s : map.keySet()) {
                if (s.contains("姓名")) {
                    System.out.print(map.get(s)+"\n\t");
                }
            }
        }
        System.out.println("-------------end----------");*/
    }
    /**
     * 获取workbook
     *
     * @return
     */
    private Workbook getWorkbook(File file){
        Workbook wb = null;
        try {
            FileInputStream fis = new FileInputStream(file);
            return new XSSFWorkbook(fis);
        }catch (IOException e){
            log.error("导入数据出错",e);
            return wb;
        }
    }

    /**
     * int CELL_TYPE_NUMERIC = 0;
     * int CELL_TYPE_STRING = 1;
     * int CELL_TYPE_FORMULA = 2;
     * int CELL_TYPE_BLANK = 3;
     * int CELL_TYPE_BOOLEAN = 4;
     * int CELL_TYPE_ERROR = 5;
     * @param
     * @return
     */
    private Object getValue(Cell cell) {
        Object obj = null;
        switch (cell.getCellType()) {
            case BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case ERROR:
                obj = cell.getErrorCellValue();
                break;
            case NUMERIC:
                obj = cell.getNumericCellValue();
                break;
            case STRING:
                obj = cell.getStringCellValue();
                break;
            default:
                break;
        }
        return obj;
    }

    /**
     * 获取map
     * @param end
     * @param row
     * @param tableTitle
     * @return
     */
    private Map<String,Object> getMap(int end,Row row,List<String> tableTitle){
        Map<String,Object> map = new HashMap<>();
        //获取数据并组装对象数据
        for (int j = 0; j < end; j++) {
            Cell cell = row.getCell(j);
            if (cell == null) {
                log.debug("null" + "\t");
                continue;
            }
            Object obj = getValue(cell);
            if (null==obj||(null != obj && "".equals((String.valueOf(cell)).trim()))){
                //导入结束，退出....
                System.out.println("导入结束，退出...");
                return null;
            }
            //组装数据
            map.put(tableTitle.get(j),String.valueOf(obj));
        }
//        System.out.println("打印数据对象："+map);
        return map;
    }

    /**
     * 获取list对象
     * @param end
     * @param row
     * @return
     */
    private List<String> getList(int end,Row row){
        List<String> list = new ArrayList<>();
        //获取数据并组装对象数据
        for (int j = 0; j < end; j++) {
            Cell cell = row.getCell(j);
            if (cell == null) {
                System.out.print("null" + "\t");
                continue;
            }
            Object obj = getValue(cell);
            if (null==obj||(null != obj && "".equals((String.valueOf(cell)).trim()))){
                //导入结束，退出....
                System.out.println("导入结束，退出...");
                return null;
            }
            list.add(String.valueOf(obj));
        }
        //System.out.println("打印表格头："+list.toString());
        return list;
    }
 }
